import mysql.connector
from mysql.connector import Error
from config import MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB
from passlib.context import CryptContext

pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

class MySQLUtils:
    def __init__(self):
        self.connection = None
        self._create_database_and_tables()
    
    def _get_connection(self):
        """获取数据库连接"""
        if not self.connection or self.connection.is_connected() is False:
            try:
                self.connection = mysql.connector.connect(
                    host=MYSQL_HOST,
                    port=MYSQL_PORT,
                    user=MYSQL_USER,
                    password=MYSQL_PASSWORD,
                    database=MYSQL_DB
                )
            except Error as e:
                print(f"MySQL connection error: {e}")
                raise
        return self.connection
    
    def _create_database_and_tables(self):
        """创建数据库和表（如果不存在）"""
        try:
            # 先连接到默认数据库创建新数据库
            temp_conn = mysql.connector.connect(
                host=MYSQL_HOST,
                port=MYSQL_PORT,
                user=MYSQL_USER,
                password=MYSQL_PASSWORD
            )
            
            cursor = temp_conn.cursor()
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS {MYSQL_DB}")
            temp_conn.close()
            
            # 连接到新数据库并创建表
            conn = self._get_connection()
            cursor = conn.cursor()
            
            # 创建用户表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                password_hash VARCHAR(255) NOT NULL,
                email VARCHAR(100) UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """)
            
            conn.commit()
        except Error as e:
            print(f"Error creating database/tables: {e}")
    
    def verify_password(self, plain_password, hashed_password):
        """验证密码"""
        return pwd_context.verify(plain_password, hashed_password)
    
    def get_password_hash(self, password):
        """获取密码哈希"""
        return pwd_context.hash(password)
    
    def get_user(self, username):
        """根据用户名获取用户信息"""
        try:
            conn = self._get_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
            return cursor.fetchone()
        except Error as e:
            print(f"Error getting user: {e}")
            return None
    
    def create_user(self, username, password, email=None):
        """创建新用户"""
        try:
            password_hash = self.get_password_hash(password)
            conn = self._get_connection()
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO users (username, password_hash, email) VALUES (%s, %s, %s)",
                (username, password_hash, email)
            )
            conn.commit()
            return True
        except Error as e:
            print(f"Error creating user: {e}")
            return False
    
    def close(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
